-- Saventh CRM – Initial Schema
-- Run this in Supabase SQL Editor or: supabase db push

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =============================================================================
-- UTILITY MASTERS (Lookup Tables)
-- =============================================================================

CREATE TABLE user_roles (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(50) NOT NULL UNIQUE,
  is_active BOOLEAN DEFAULT true,
  sort_order INT DEFAULT 0
);
INSERT INTO user_roles (name, sort_order) VALUES ('Admin', 1), ('Presales', 2), ('RM', 3);

CREATE TABLE industries (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(100) NOT NULL UNIQUE,
  is_active BOOLEAN DEFAULT true,
  sort_order INT DEFAULT 0
);
INSERT INTO industries (name, sort_order) VALUES
  ('Information Technology (IT Services / Software)', 1), ('Banking & Financial Services', 2), ('Fintech', 3),
  ('Insurance', 4), ('Manufacturing', 5), ('Automotive', 6), ('Pharmaceuticals', 7), ('Healthcare & Hospitals', 8),
  ('Education & EdTech', 9), ('Real Estate', 10), ('Construction & Infrastructure', 11), ('Oil & Gas', 12),
  ('Energy & Utilities', 13), ('Retail', 14), ('E-commerce', 15), ('FMCG', 16), ('Logistics & Supply Chain', 17),
  ('Telecommunications', 18), ('Media & Advertising', 19), ('Hospitality & Travel', 20), ('Aviation', 21),
  ('Government & Public Sector', 22), ('Consulting', 23), ('Legal Services', 24), ('HR & Staffing', 25),
  ('BPO / KPO', 26), ('Mining & Metals', 27), ('Agriculture & AgriTech', 28),
  ('Startups / Tech Product Companies', 29), ('NGOs / Non-Profit', 30);

CREATE TABLE opportunity_stages (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(50) NOT NULL UNIQUE,
  sort_order INT DEFAULT 0,
  is_active BOOLEAN DEFAULT true
);
INSERT INTO opportunity_stages (name, sort_order) VALUES
  ('Cold', 1), ('Email', 2), ('Virtual Call Scheduled', 3),
  ('Assigned to RM', 4), ('Proposal Sent', 5), ('Converted', 6), ('Dropped', 7);

CREATE TABLE seminar_modes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(50) NOT NULL UNIQUE,
  is_active BOOLEAN DEFAULT true,
  sort_order INT DEFAULT 0
);
INSERT INTO seminar_modes (name, sort_order) VALUES ('Virtual', 1), ('On Ground', 2);

CREATE TABLE corporate_statuses (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(50) NOT NULL UNIQUE,
  is_active BOOLEAN DEFAULT true,
  sort_order INT DEFAULT 0
);
INSERT INTO corporate_statuses (name, sort_order) VALUES ('Prospect', 1), ('In Discussion', 2), ('Empanelled', 3), ('Not Empanelled', 4);

CREATE TABLE designations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(100) NOT NULL UNIQUE,
  is_active BOOLEAN DEFAULT true,
  sort_order INT DEFAULT 0
);
INSERT INTO designations (name, sort_order) VALUES ('HR Coordinator', 1), ('HR Head', 2), ('Finance', 3), ('CXO', 4), ('Admin', 5);

CREATE TABLE lead_sources (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(100) NOT NULL UNIQUE,
  is_active BOOLEAN DEFAULT true,
  sort_order INT DEFAULT 0
);
INSERT INTO lead_sources (name, sort_order) VALUES ('Apollo', 1), ('LinkedIn', 2), ('Referral', 3), ('Cold Call', 4), ('Website', 5), ('Event', 6);

CREATE TABLE locations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(100) NOT NULL UNIQUE,
  is_active BOOLEAN DEFAULT true,
  sort_order INT DEFAULT 0
);
INSERT INTO locations (name, sort_order) VALUES ('State', 1), ('City', 2), ('Region', 3);

CREATE TABLE followup_statuses (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(50) NOT NULL UNIQUE,
  is_active BOOLEAN DEFAULT true,
  sort_order INT DEFAULT 0
);
INSERT INTO followup_statuses (name, sort_order) VALUES ('Pending', 1), ('Completed', 2), ('Rescheduled', 3), ('No Response', 4);

CREATE TABLE product_interests (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(100) NOT NULL UNIQUE,
  is_active BOOLEAN DEFAULT true,
  sort_order INT DEFAULT 0
);
INSERT INTO product_interests (name, sort_order) VALUES ('SIP', 1), ('Mutual Funds', 2), ('Tax Saving', 3), ('Financial Planning', 4);

-- =============================================================================
-- MAIN TABLES
-- =============================================================================

CREATE TABLE users (
  user_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  role UUID NOT NULL REFERENCES user_roles(id),
  full_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash TEXT,
  employee_code VARCHAR(50),
  phone VARCHAR(50),
  address TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE corporates (
  corporate_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(255) NOT NULL,
  address TEXT,
  industry_id UUID REFERENCES industries(id),
  employee_base INT,
  corporate_number VARCHAR(100),
  number_of_locations INT,
  status_id UUID NOT NULL REFERENCES corporate_statuses(id),
  assigned_rm_id UUID REFERENCES users(user_id),
  created_by UUID REFERENCES users(user_id),
  lead_source_id UUID REFERENCES lead_sources(id),
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE pocs (
  poc_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  corporate_id UUID NOT NULL REFERENCES corporates(corporate_id),
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  designation_id UUID REFERENCES designations(id),
  phone VARCHAR(50),
  location_id UUID REFERENCES locations(id),
  authority_level VARCHAR(50),
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE branches (
  branch_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  corporate_id UUID NOT NULL REFERENCES corporates(corporate_id),
  address TEXT,
  location_id UUID REFERENCES locations(id),
  employee_strength INT,
  poc_id UUID REFERENCES pocs(poc_id)
);

CREATE TABLE opportunities (
  opportunity_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  corporate_id UUID NOT NULL REFERENCES corporates(corporate_id),
  poc_id UUID REFERENCES pocs(poc_id),
  stage_id UUID NOT NULL REFERENCES opportunity_stages(id),
  opportunity_cost DECIMAL(15, 2),
  followup_count INT DEFAULT 0,
  assigned_user_id UUID REFERENCES users(user_id),
  lead_source_id UUID REFERENCES lead_sources(id),
  comment TEXT,
  dropped_comment TEXT,
  converted_comment TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE followups (
  followup_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  opportunity_id UUID NOT NULL REFERENCES opportunities(opportunity_id),
  corporate_id UUID REFERENCES corporates(corporate_id),
  poc_id UUID REFERENCES pocs(poc_id),
  stage VARCHAR(50),
  comment TEXT,
  reschedule_status BOOLEAN DEFAULT false,
  reschedule_datetime TIMESTAMPTZ,
  followup_status_id UUID REFERENCES followup_statuses(id),
  created_by UUID REFERENCES users(user_id),
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE seminars (
  seminar_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  corporate_id UUID NOT NULL REFERENCES corporates(corporate_id),
  branch_id UUID REFERENCES branches(branch_id),
  seminar_date DATE,
  mode_id UUID REFERENCES seminar_modes(id),
  location VARCHAR(255),
  committed_attendees INT,
  actual_attendees INT,
  poc_id UUID REFERENCES pocs(poc_id),
  created_by UUID REFERENCES users(user_id),
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE qr_sessions (
  qr_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  seminar_id UUID NOT NULL REFERENCES seminars(seminar_id),
  presentation_template VARCHAR(255),
  session_name VARCHAR(50),
  qr_url TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE seminar_scans (
  scan_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  seminar_id UUID NOT NULL REFERENCES seminars(seminar_id),
  corporate_id UUID REFERENCES corporates(corporate_id),
  name VARCHAR(255),
  email VARCHAR(255),
  product_interest_id UUID REFERENCES product_interests(id),
  interest_label VARCHAR(100),
  review_requested BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE external_signals (
  external_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  corporate_id UUID NOT NULL REFERENCES corporates(corporate_id),
  google_review_link TEXT,
  linkedin_page_link TEXT,
  instagram_page_link TEXT
);

-- =============================================================================
-- INDEXES (for common queries)
-- =============================================================================
CREATE INDEX idx_corporates_assigned_rm ON corporates(assigned_rm_id);
CREATE INDEX idx_corporates_status ON corporates(status_id);
CREATE INDEX idx_pocs_corporate ON pocs(corporate_id);
CREATE INDEX idx_opportunities_corporate ON opportunities(corporate_id);
CREATE INDEX idx_opportunities_stage ON opportunities(stage_id);
CREATE INDEX idx_followups_opportunity ON followups(opportunity_id);
CREATE INDEX idx_seminars_corporate ON seminars(corporate_id);
CREATE INDEX idx_seminar_scans_seminar ON seminar_scans(seminar_id);
